--> Create Standarized Database

USE master if exists(
SELECT * 
FROM sys.databases
WHERE name = 'GE_Standardized')
DROP DATABASE GE_Standardized

CREATE DATABASE GE_Standardized

--cmd

CREATE TABLE  GE_Standardized.dbo.engr_units (
engr_unit_id int,
name varchar(30),
abbrev varchar(30),
mult_to_ref real,
offset_to_ref real,
constraint pkengr_unit_id primary key(engr_unit_id)
)

CREATE TABLE GE_Standardized.dbo.waveform_status (
status_id int,
[status] varchar(30),
constraint pkstatus_id primary key(status_id)
)

CREATE TABLE GE_Standardized.dbo.waveform (
segment_id int,
gmt datetime,
machine_rpm real,
sample_rate int,
bits_per_sample int,
sample_data image,
engr_unit_id int,
status_id int,
constraint pksegment_id_gmt primary key(segment_id,gmt),
constraint fkengr_unit_id foreign key(engr_unit_id) REFERENCES engr_units(engr_unit_id),
constraint fkstatus_id foreign key(status_id) REFERENCES waveform_status(status_id)
)

CREATE TABLE GE_Standardized.dbo.gearbox (
gearbox_id int,
serial_number varchar(30),
site_location varchar(30),
tag varchar(30),
workorder varchar(30),
gearbox_make varchar(30),
constraint pkgearbox_id primary key(gearbox_id)
)

CREATE TABLE GE_Standardized.dbo.stage (
stage_id int,
[type] varchar(30),
designation bit,
pinion_tooth_counth int,
gear_tooth_count int,
sun_tooth_count int,
planet_tooth_count int,
ring_tooth_count int,
planet_count int,
fixed tinyint,
gearbox_id int,
constraint pkstage_id primary key(stage_id),
constraint fkgearbox_id foreign key(gearbox_id) REFERENCES gearbox(gearbox_id)
)

CREATE TABLE GE_Standardized.dbo.data_source (
data_source_id tinyint,
name varchar(30),
constraint pkdata_source_id primary key(data_source_id)
)

CREATE TABLE GE_Standardized.dbo.gearbox_condition (
gearbox_id int,
[start_date] datetime,
end_date datetime,
condition varchar(30),
data_source_id tinyint
constraint pkgearbox_id_start_date primary key (gearbox_id,[start_date]),
constraint fkgearbox_id_ foreign key(gearbox_id) REFERENCES gearbox(gearbox_id),
constraint fkdata_source_id foreign key (data_source_id) REFERENCES data_source(data_source_id)
)


CREATE TABLE GE_Standardized.dbo.segment (
segment_id int,
point_name varchar(30),
stage_id int,
constraint pksegment_id primary key(segment_id),
constraint fkstage_id foreign key(stage_id) REFERENCES stage(stage_id)
)